<?php
class Mglobal extends CI_Model {
	
	function __construct(){
		parent::__construct();
		$this->is_logged_in();
		//$this->punya_hak_akses();
	}
	
	function is_logged_in()
	{
		//$tendang = $this->showdata('lookup_value','t_mtr_lookup',array('lower(lookup_key)'=>'restricted_time_start'),'dblokal');
		$is_logged_in = $this->session->userdata('is_logged_in');
		if( $this->uri->segment(1) <>"login" && $this->uri->segment(1) <>"mobile" &&
		   ( !isset($is_logged_in) || $is_logged_in != true))
		{
			redirect('/login/'.$this->core->encode($this->uri->uri_string()));
		}
	}
	
	function punya_hak_akses(){
		/*Proses pemeriksaan URL, boleh diakses/tidak?
		1. List URL yang gak boleh diakses
		2. Kalo ternyata ada, maka REDIRECT ke main
		*/
		$dblokal  = $this->load->database("default", TRUE);
		$userLogin = $this->session->userdata('username');
		
		$boleh = "";
		$url = $this->uri->uri_string();
		
		$query = $dblokal->query("SELECT t_menu.alias
								 FROM t_menu
								WHERE published=1 AND id NOT IN (
									
									SELECT t_mtr_user_role.menu_id
									FROM t_mtr_user_role, t_mtr_user
									WHERE t_mtr_user_role.user_group_id = t_mtr_user.user_group_id
										AND t_mtr_user.user_id='$userLogin'
									
								) ");
		foreach($query->result() as $r){
			
			$mystring = $url;
			$findme   = $r->alias;
			if($findme <> ""){
				$pos = strpos($mystring, $findme);
				if ($pos === false) {
					$boleh .= "";
				} else {
					$boleh .= "ada"; //ada, berarti ada URL yg dipaksa dilihat
				}
			}
		}
		
		if( $this->uri->segment(1) <>"main" && $this->uri->segment(1) <>"login" && $this->uri->segment(1) <>"mobile" && $boleh <> "" )
		{
			redirect('/main/welcome');
		}
	}
	
	function saveLog($key,$act,$id,$client="web",$user_id=""){
		$dblokal  = $this->load->database("default", TRUE);
		$ip = $this->input->ip_address();
        if($client=="web")
            {
                $log = Array(
                    "log_key"    => $key,
                    "log_action" => "action=$act; id=$id; client=$client; ipAddr=$ip",
		        				"user_id"    => $this->session->userdata('username')
		        		);
            }
     
     else    
              {
                  $log = Array(
                      "log_key"    => $key,
                      "log_action" => "action=$act; client=$client; ipAddr=$ip",
                      "user_id"    => $id
                  );
              }

			$dblokal->insert("t_log", $log);
	}
	
	function get_is_self_service($user_group_id)
	{
		$this->db->select('is_self_service');
		$query = $this->db->get_where('t_mtr_user_group', array('user_group_id'=>$user_group_id));
		foreach ($query->result() as $r) {
			return $r->is_self_service;
		}
	}

	function showData( $data, $tbl, $where, $db, $like = "" )
        {    
                $dblokal  = $this->load->database("default", TRUE);
                
                ${$db}->where($where);
                if($like <> "") ${$db}->like($like);
				$query = ${$db}->get($tbl);
                $val=1;
                if($val == 0){
                        return "";
                }else{
                        if($query->num_rows() > 0) {
                                $r = $query->row();
                                return $r->{$data};
                        } else {
                                return "";
                        }       
                }
	}
	
	function countData( $data, $tbl, $where, $db, $like = "" )
        {    
                $dblokal  = $this->load->database("default", TRUE);
                
                ${$db}->where($where);
                if($like <> "") ${$db}->like($like);
		$query = ${$db}->get($tbl);
                return $query->num_rows();
	}
	
	function week_start_date($wk_num, $yr, $first = 2, $format = 'F d, Y') 
	{ 
		$wk_ts  = strtotime('+' . $wk_num . ' weeks', strtotime($yr . '0101')); 
		$mon_ts = strtotime('-' . date('w', $wk_ts) + $first . ' days', $wk_ts); 
		return date($format, $mon_ts);
	}
	
	function getDays($week,$year){
		$sStartDate = $this->week_start_date($week-1, $year); 
		$sEndDate   = date('F d, Y', strtotime('+6 days', strtotime($sStartDate)));
		return $sStartDate . " - ". $sEndDate;
	}
	
	function getWorkingDay($jenis,$bulan,$tahun){
		$dblokal  = $this->load->database("default", TRUE);
		$days = $this->mglobal->showdata("lookup_value","t_mtr_lookup",array('lookup_key'=>'workday_'.$jenis.'_'.$tahun.$bulan),"dblokal");
		return $days;
	}
	
	
	////fungsi UMUM
	function getTerritoryPath(){
		$dblokal  = $this->load->database("default", TRUE);
		$userLogin = $this->session->userdata('username');
		$query = $dblokal->query("select f_get_territory_path(f_get_cluster('$userLogin')) as hasil");
		$hasil="";
		if($query->num_rows() >0){
			$r = $query->row();
            $hasil = $r->hasil;
		}
		// $hasil="-sampel-";
		return $hasil;
	}
	
	//by: alfin akhret
	function getSFA_is_self_service($user_id)
	{
		$dblokal  = $this->load->database("default", TRUE);
		$userLogin = $this->session->userdata('username');
		$query = $dblokal->query("SELECT user_id, CONCAT_WS(' | ',user_id,initcap(user_name)) as uname
									FROM t_mtr_user WHERE user_id = '$user_id' AND istatus = 1
									ORDER BY user_name ASC;");
		return $query->result();
	}
	
	function getSFA(){
		$dblokal  = $this->load->database("default", TRUE);
		$userLogin = $this->session->userdata('username');
		/** @edited Alfin Akhret <alfin.akhret@gmail.com>
            /* optimasi query
            */
            $cluster = $this->db->query("select * from f_get_cluster('".$userLogin."')");
            $cluster = $cluster->result();
            $cluster = $cluster[0]->f_get_cluster;
            // echo $cluster;die();

		$query = $dblokal->query("SELECT user_id, CONCAT_WS(' | ',user_id,initcap(user_name)) as uname
									FROM t_mtr_user
									WHERE user_group_id = 8 AND istatus = 1
									AND f_get_cluster(user_id) = '$cluster'
									ORDER BY user_name ASC;");
		
		return $query->result();
	}
	
	function getDS(){ 
		$dblokal  = $this->load->database("default", TRUE);

		$userLogin = $this->session->userdata('username');
		/** @edited Alfin Akhret <alfin.akhret@gmail.com>
            /* optimasi query
            */
            $cluster = $this->db->query("select * from f_get_cluster('".$userLogin."')");
            $cluster = $cluster->result();
            $cluster = $cluster[0]->f_get_cluster;
            // echo $cluster;die();

		$query = $dblokal->query("SELECT user_id, CONCAT_WS(' | ',user_id,initcap(user_name)) as uname
									FROM t_mtr_user
									WHERE user_group_id = 9 AND istatus = 1
									AND f_get_cluster(user_id) = '$cluster'
									ORDER BY user_name ASC;");
		
		return $query->result();
	}
	
	function getGroupId(){
		$dblokal  = $this->load->database("default", TRUE);
		$q = $dblokal->query("SELECT user_group_id
										FROM t_mtr_user_group
										WHERE user_group_caption = 'SGS' LIMIT 1;");
		$userGroupId="";
		if($q->num_rows()>0){
			$row = $q->row();
			$userGroupId = $row->user_group_id;
		}
		return $userGroupId;
	}
	
	function getSGS(){ 
		$dblokal  = $this->load->database("default", TRUE);
		$userLogin = $this->session->userdata('username');
		/** @edited Alfin Akhret <alfin.akhret@gmail.com>
            /* optimasi query
            */
            $cluster = $this->db->query("select * from f_get_cluster('".$userLogin."')");
            $cluster = $cluster->result();
            $cluster = $cluster[0]->f_get_cluster;
            // echo $cluster;die();

		$userGroupId=$this->getGroupId();
		$query = $dblokal->query("SELECT user_id as mvalue, CONCAT_WS(' | ',user_id, user_name) as mdisplay
								FROM t_mtr_user
								   WHERE user_group_id = $userGroupId 
								   AND istatus = 1
								   AND f_get_cluster(user_id) = '$cluster'
								   ORDER BY user_name ASC;");
		
	   return $query->result();
	}
	
	function getSales(){
		$dblokal  = $this->load->database("default", TRUE);
		$userLogin = $this->session->userdata('username');
		$query = $dblokal->query("SELECT usr.user_id, CONCAT_WS(' | ',usr.user_id,initcap(usr.user_name),grp.user_group_caption) as uName
									FROM t_mtr_user usr
									JOIN t_mtr_user_group grp ON usr.user_group_id = grp.user_group_id
									WHERE grp.parent_id = 4 AND usr.istatus = 1
									AND f_get_cluster(usr.user_id) = f_get_cluster('$userLogin')
									ORDER BY usr.user_name ASC;");
		return $query->result();
	}

	// yaddis@nutech 09/19/2012
	// to replace function getSales() above
	// $cluster_id = integer, 
	// $sales_group = array(8,9,31), 8 = SFA, 9 = DS, 31 = SGS, 35 = SGA
	function get_sales($cluster_id, $sales_group) {
		$this->db->select("uu.user_id, gg.user_group_caption, gg.user_group_id, initcap(uu.user_name) as user_name", false );
		$this->db->join("t_mtr_user_group AS gg","gg.user_group_id = uu.user_group_id");
		$this->db->where("uu.istatus", 1);
		$this->db->where("f_get_cluster(uu.user_id)", $cluster_id, false);
		$this->db->where_in("uu.user_group_id", $sales_group );
		$this->db->order_by("gg.user_group_caption, uu.user_name", "asc");
		$query = $this->db->get("t_mtr_user AS uu");
		return $query->result();
	}
	
	function set_is_login($val=0,$id=""){
		$dblokal  = $this->load->database("default", TRUE);
		if($id == "") $id = $this->session->userdata('username');
		$dblokal->query("UPDATE t_mtr_user SET is_login=$val WHERE user_id='".$id."'" );
	}
	
	function get_is_login(){
		return $this->showdata('is_login','t_mtr_user',array('user_id'=>$this->session->userdata('username')),'dblokal');
	}

	//added by : alfin akhret
	function set_must_change_password($user_id, $val)
	{
		$this->db->where('user_id', $user_id);
		$this->db->update('t_mtr_user', array('must_change_password'=>$val));
	}

	//updated by : Alfin Akhret
	function get_pass_length()
	{
		$this->db->select('lookup_value');
		$query = $this->db->get_where('t_mtr_lookup', array('lookup_key'=>'account_min_length'));
		return $query->row()->lookup_value;
	}

	public function user_cluster($sales_id)
	{
		$this->db->select("f_get_cluster('$sales_id') as cluster_id");
		$query = $this->db->get();
		return $query->row()->cluster_id;
	}

  public function user_region($cluster_id)
  {
  	$this->db->select('parent_id');
  	$query = $this->db->get_where('t_mtr_territory', array('territory_id' => $cluster_id));
  	return @$query->row()->parent_id;
  }

	public function get_territory( $cluster_id ){
		$this->db->select("f_get_territory_path($cluster_id) as territory");
		$query = $this->db->get();
		return $query->row()->territory;
	}	

  public function f_get_territory_id_by_channel_id($partner_id) 
  {
    $this->db->select("f_get_territory_id_by_channel_id('$partner_id')", false);
    return $this->db->get()->row()->f_get_territory_id_by_channel_id;
  }


}